エクセル関数でシフト表を自動作成する方法｜無料テンプレートダウンロード可能

エクセルでシフト表を管理すると、日付を変更したり、土日欄に色をつけたりしなくてはならず、手間がかかります。

こうした場合に、関数や各種エクセルの機能を使うと、多くの作業を自動化することができます。

例えば、以下のような作業です。

この記事では、上記のように各種作業を自動化して管理がラクになるシフト表の作り方を紹介します。

また紹介しているシフト表をダウンロードできるようにしたので、興味がある人はダウンロードして使ってみてください。

動画解説｜エクセル関数でシフト表を自動作成する方法

以下の動画で作成手順をゼロから解説しています。

まずはこちらをご覧ください。

シフト表の3つの機能｜カレンダー、時間計算、時間帯

この記事では、以下の機能をもつようなシフト表を作成していきます。

機能1｜シフト表をカレンダーで閲覧する

この記事で紹介するシフト表では、カレンダーをガントチャート風に右に伸ばしていくことで、記入していきます。

動画で紹介しているとおり、開始日と終了日を入力することで自動で日付を入力することが可能です。

機能2｜シフト表の総業務時間を計算する

この記事で紹介するシフト表では、シフト表の下部で業務日数を確認することができます。

総業務時間はシフト表下部の業務日数にシフト別の業務時間をかけ算することで算出可能です。

また人員が「0」の場合は、セルが黄色くなり、不足している人員を可視化することもできます。

機能3｜シフト表の時間帯を選択できる

シフト表上部の一覧表で、「シフトの分類」を作成・管理することができます。

分類ごとに「開始」「終了」時間を入力することで業務時間を変更可能です。

また一覧表を拡張すれば、シフトの分類を増やすこともできます。

エクセル関数でシフト表を作成する手順

以下で詳細を解説していきます。

手順1｜必要情報を埋めていく

はじめに、必要となる次の情報を入力していきます。

ステップ1｜開始日と終了日

画像のように「開始日」と「終了日」の一覧表を「B2:C3」の範囲に作成します。

この時、「Ctrl + ;」で現在の日付を入力することができるので、日付入力の際に使ってみましょう。

ステップ2｜No, 氏名, 役職

次に、「No」「 氏名」「 役職」を入力します。

「B6」「C6」「D6」に文字列を入力したら、それぞれのセルを一つ下のセルと結合しておきましょう。

続いて、人員の数に応じて、罫線を引きます。罫線を引きたいセル範囲を選択した状態で「Alt → H → B → A」と操作すると一気に罫線を引くことができます。

ステップ3｜日付

日付を入力します。

まず「E6」に開始日「2020/4/1」を入力し、「F6」に「=E6 + 1」と入力します。これで「F6」には「2020/4/2」と表示されます。
次に、「F6」から終了日までのセル範囲を選択して、「Ctrl + R」を押します。すると、「F6」に入力した数式が終了日セルまで入力されます。

続いて、「2020/4/1」を「4/1」という表示にするため、日付セルの表示形式を修正します。

次の手順で操作します。

1. ホームタブのにある書式横のドロップダウンを開く
2. 「その他の表示形式(M)」をクリック
3. 「セルの書式設定」内の「日付」から「3/14」を選択
4. 「OK」をクリック

これで、選択していたセルの表示形式が「2020/4/1」から「4/1」となります。

最後に、日付が入力されている列を選択し、列幅を「4.5」に修正します。これで、日付の入力は完了です。

ステップ4｜曜日

日付ごとに曜日を入力します。

一つずつ曜日を入力するのではなく、「=TEXT(値, 表示形式)」という関数を使用します。
例えば、「E7」に入力する数式は「=TEXT(E6,”aaa”)」となります。これで、一つ上のセル「E6」の日付に対する曜日が一文字で表示されます。

あとは、「E7」セルを終了日までコピーすれば、各日付ごとに曜日を表示することができます。

ステップ5｜見た目を整える

値の入力ができたら、最後に画像のように見た目を整えます。

まず、日付と曜日が入力されているセルの表示を「中央揃え」にします。
続いて、シフト表全体に罫線を引きます。罫線を引きたいセルを選択して「Alt → H → B → A」と押すことで罫線を引くことができます。

これで、シフト表の必要情報が全て入力できました。

手順2｜シフトの分類を記載していく

次にシフトの分類を一覧表に記載していきます。
画像のように、「E2:K4」セルに一覧表を追加します。

なお、分類の数が多い場合は枠を増やすことで対応できます。

手順3｜開始日と終了日を記入したら、カレンダーが自動で出力されるようにする

続いて、開始日と終了日から、自動でシフト表の日付を出力されるように修正を加えていきます。

画像のように、開始日である「E6」セルには「=C2」と入力して「開始日」に入力した日付が表示されるようにします。
次に、「F6」セルに「=IF(E6+1>$C$3,””,E6+1)」と入力します。ここで使用しているのは「IF(論理式, 真の場合, 偽の場合)」を利用した数式です。

「F6」ではまず、論理式が「E6+1>$C$3」となっています。これは、「F6」から見て一つ左のセル「E6」に「1」を足した数、つまり「E6」の翌日が終了日「$$C$3」よりも大きいことを表しています。

なので、論理式が真の場合、つまり「左隣の日付翌日が終了日より大きい」場合は真の値「””」（空白）を表示します。
逆に、論理式が偽の場合、つまり「左隣の日付翌日が終了日以下」の場合は、偽の値「E6+1」を表示します。

あとは、「F6」の数式を残りの日付セルにコピーすれば「開始日」と「終了日」から自動でカレンダーが出力されるようになります。

手順4｜土日を条件付き書式で色を変える

次は、画像のようにカレンダーの内、土日の列に色をつけていきます。

手動で色をつけるのではなく、「条件付き書式」という機能を使用して日付と連動して自動で色がつくように設定していきます。

まずは、「日曜」列に自動で色付けをする設定をしていきます。
下記の手順で操作しましょう。

1. 「E6」セルを選択
2. ホームタブの「条件付き書式」をクリック
3. 「ルールの管理(R)」をクリック
4. 上部のドロップダウンリストから「このワークシート」を選択

続いて、次の手順で条件を設定します。

1. 「新規ルール(N)」をクリック
2. 「数式を使用して、書式設定するセルを決定」を選択
3. 「次の数式を満たす場合に値を書式設定(O):」に数式「=weekday(E$6)=1」を入力
4. 「書式(F)」をクリックして、適用したい書式を設定

最後に、画像のように適用先を「$E$6:$AI$23」として「適用」をクリックすれば、設定は完了です。

「土曜」列に色付けする場合も同様の手順で、「次の数式を満たす場合に値を書式設定(O):」に入力する数式を次のように修正すれば設定完了です。

• 日曜日：=weekday(E$6)=1
• 土曜日：=weekday(E$6)=7

手順5｜祝日を条件付き書式で色を変える

次に、土日のセルと同様に、祝日のセルにも自動で色付けされるように設定していきます。

まず、「祝日」がいつかを把握するため、次の手順で「祝日」マスタを作成します。

1. 「祝日」という名前のシートを追加
2. こちらから「祝日マスタ」をコピー
3. 「祝日」シートに「テキスト」で貼り付ける

次に、作成した「祝日マスタ」を利用して、条件付き書式を設定します。

1. 「E6」セルを選択
2. ホームタブの「条件付き書式」をクリック
3. 「ルールの管理(R)」をクリック
4. 上部のドロップダウンリストから「このワークシート」を選択
5. 「新規ルール(N)」をクリック
6. 「数式を使用して、書式設定するセルを決定」を選択
7. 「次の数式を満たす場合に値を書式設定(O):」に数式「=COUNTIF(祝日!$B:$B,E$6)=1」を入力
8. 「書式(F)」をクリックして、適用したい書式を設定
9. 条件の「適用先」に「=$E$6:$AI$23」を入力

※条件式として入力した「=COUNTIF(祝日!$B:$B,E$6)=1」は動画とは違う条件式ですが、動きは同様でこちらの方が簡単です。

条件式「=COUNTIF(祝日!$B:$B,E$6)=1」は、「祝日マスタ」のB列内にカレンダー上の日付がいくつあるかを取得して、それが「1」だった場合に書式が適用されるよう設定されています。

これで土日に加えて、祝日についても色がつくように設定できました。

手順6｜シフト表にシフトを入力する

シフト表へ、画像のように、シフトの分類で記入していきます。

手順7｜COUNTIFでシフト数をカウント

各シフトの分類がいくつあるかを自動でカウントさせて表示させます。画像のようにシフト数が「0」の場合はセルを黄色で表示します。

まず、「E18」セルを選択して、数式「=COUNTIF(E$8:E$17,$B18)」を入力します。
これで、シフトの分類を入力している「E$8:E$17」の範囲に「B18」のシフトの分類がいくつ入力されているかを表示することができます。

あとは同様の数式を「$E$18:$AI$23」にコピーすれば、日別にシフト数をカウントすることができます。

続いて、表示が「0」だった場合に、黄色く塗りつぶす条件付き書式を設定します。

1. 「E18」セルを選択
2. ホームタブの「条件付き書式」をクリック
3. 「ルールの管理(R)」をクリック
4. 上部のドロップダウンリストから「このワークシート」を選択
5. 「新規ルール(N)」をクリック
6. 「数式を使用して、書式設定するセルを決定」を選択
7. 「次の数式を満たす場合に値を書式設定(O):」に数式「=E18=0」を入力
8. 「書式(F)」をクリックして、適用したい書式を設定
9. 条件の「適用先」に「=$E$18:$AI$23」を入力

以上で、シフト分類ごとにシフト数をカウントし、シフト数が「0」の場合は黄色で表示するようにすることができました。

手順8｜見た目を整える

最後に、見た目を整えたら完成です。

画像のようにシフト表以外の部分を選択した状態で、塗りつぶしを実行しましょう。

動画で紹介しているシフト表のテンプレートを無料ダウンロードする

この記事で紹介しているシフト表のテンプレートは以下のフォームから無料でダウンロード可能です。

ぜひ、ダウンロードして活用してみてください。

日別のシフト表も紹介しています｜こちらの記事からダウンロード可能

この記事では、月別のシフト表を紹介しましたが、日別のシフト表を使いたい人もいるかもしれません。

以下の記事で、日別のシフト表の作り方を紹介しています。また、関数とマクロが入ったエクセルをダウンロード可能です。

興味がある人は、ぜひご覧ください。

エクセル関数で仕事で使えるテンプレートを作ってみる方法はこちらで紹介

エクセル関数を上手く使えるようになれば、シフト表以外も作成可能です。

たとえば、以下のようなテンプレートを作成可能です。

以下の記事で動画で紹介しているので、興味があればぜひご覧ください。

エクセルマクロVBAでシフト表を作ってみる

この記事では、エクセルマクロVBAを使った方法は紹介していません。

しかしマクロVBAを使えば、さらに機能を増やすことができます。

一か月分の集計から、報告資料作成までうまく連携させることが可能です。

エクセルマクロVBAで出来ることを以下の動画でまとめているので、それらをシフト表を組み合わせることでさらにラクに仕事をこなすことが可能です。

もしエクセルマクロVBAを勉強したいのであれば、こちら(無料)の動画講座をオススメします。

私も実際に試して、良い教材であることは確認済みです。

実際に教材を試して、以下のように短期間で難易度の高いマクロを書けるようになりました。

また「パソコンスキルの教科書」のYoutubeチャンネルに参加いただけると情報をいち早く受け取れるので、ぜひ登録をお願いいたします。